package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


import org.apache.commons.dbutils.handlers.BeanListHandler;


import com.beans.Goodsinfo;
import com.jdbc.DBUtil;
import com.utils.PageInfo;
import com.utils.StringUtil;

public class GoodsDao {
	private BeanListHandler<Goodsinfo> handler=new BeanListHandler<Goodsinfo>(Goodsinfo.class);
	/**
	 * 添加商品
	 * 
	 * @param goods 商品信息
	 * @return
	 */
	public int addGoods(Goodsinfo goods) {
		String sql = "insert into goodsinfo(goodsName,smallCateId,price,des,unit,producter,pictureData) values ( ?,?,?,?,?,?,?,?)";
		return DBUtil.addAndReturnId(sql, goods.getGoodsName(), goods.getSmallCateId(), goods.getPrice(),
				goods.getDes(), goods.getUnit(), goods.getProducter(), goods.getPictureData());
	}
	
	/**
	 * 获取全部商品信息
	 * @return
	 */
	public List<Goodsinfo> getAllgoods(){
		return DBUtil.getList("select  a.*,b.cateName as bigCateName,c.cateName as smallCateName from goodsinfo a,cateinfo b,cateinfo c where  a.smallCateId=c.id and c.parentId=b.id ", Goodsinfo.class);
	}
	

	/**
	 * 获取指定商品信息
	 *  * @param id 商品id
	 */
	public Goodsinfo getGoods(int id){
		return DBUtil.getSingleObject("select  a.*,b.id as bigCateId from goodsinfo a,cateinfo b,cateinfo c where a.smallCateId=c.id and c.parentId=b.id and a.id=?", Goodsinfo.class,id);
	}
	
	/**
	 * 获取指定数据条目
	 */
	public int getGoodsCount(String smallCateId,String goodsName) {
		StringBuffer sql=new StringBuffer(" select count(*) from goodsinfo where 1=1 ");
		
		if(!(StringUtil.isEmpty(smallCateId) || smallCateId.equals("-1"))) {
			sql.append(" and smallCateId="+smallCateId+"");
		}
		if(!StringUtil.isEmpty(goodsName)) {
			sql.append(" and goodsName  like'%"+goodsName+"%'");
		}
		long count = DBUtil.getScalar(sql.toString());
		return Integer.parseInt(count + "");
	}
	public int getGoodsCount(String smallCateId) {
		return getGoodsCount(smallCateId,null);
	}
	
	/**
	 * 获取商品列表
	 * 
	 * @param page 分页属性
	 */
	public List<Goodsinfo> getGoodsList(PageInfo page) {
		return DBUtil.getList(
				"select  a.*,b.cateName as bigCateName,c.cateName as smallCateName from goodsinfo a,cateinfo b,cateinfo c where c.parentId=b.id and a.smallCateId=c.id limit ?, ? ",
				 Goodsinfo.class,page.getBeginRow(), page.getPageSize());
	}
	
	/**
	 * 删除商品
	 * 
	 * @param id 角色id
	 * @return
	 */
	public int delGoods(int id) {
		return DBUtil.update("delete from goodsinfo where id= ?", id);
	}
	
	/**
	 * 批量删除用户
	 * 
	 * @param strs 用户id列表
	 * @return
	 */
	public void delAllGoods(String[] strs) {
		PreparedStatement stm = null;
		try {
			Connection conn = DBUtil.getConn();
			String sql = "delete from goodsinfo where id= ?";
			stm = conn.prepareStatement(sql);

			for (String str : strs) {
				stm.setString(1, str);
				stm.addBatch(); // 添到批处理中了
			}
			stm.executeBatch(); // 执行批处理

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(null, stm);
		}
	}
	public int updateGoods(Goodsinfo goods) {
		String sql = "update goodsinfo set goodsName= ? , smallCateId= ? , price= ? , des= ? , unit= ? , producter= ? , pictureData = ? where id = ?" ;
		return DBUtil.update(sql, goods.getGoodsName(), 
				goods.getSmallCateId(), goods.getPrice(),
				goods.getDes(), goods.getUnit(), 
				goods.getProducter(), goods.getPictureData(),
				goods.getId());
	}
	
	/**
	 * 多条件复合查询商品
	 * @param bigCateId 一级商品分类
	 * @param smallCateId 二及商品分类
	 * @param goodsName 商品名称
	 * @param page 分页属性
	 * @return
	 */
	public List<Goodsinfo> getGoodsList(String smallCateId,String goodsName,PageInfo page){
		List<Goodsinfo> goodsList=new ArrayList<Goodsinfo>();
		Statement stm=null;
		ResultSet rs=null;
		
		try {
			Connection conn=DBUtil.getConn();
			stm=conn.createStatement();
			StringBuffer sql=new StringBuffer(" select  a.*,b.cateName as bigCateName,c.cateName as smallCateName from goodsinfo a,cateinfo b,cateinfo c where c.parentId=b.id and a.smallCateId=c.id ");
			
			if(!(StringUtil.isEmpty(smallCateId) || smallCateId.equals("-1"))) {
				sql.append(" and a.smallCateId="+smallCateId+"");
			}
			if(!StringUtil.isEmpty(goodsName)) {
				sql.append(" and a.goodsName  like'%"+goodsName+"%'");
			}
			sql.append(" order by id desc limit "+page.getBeginRow()+", "+page.getPageSize());
			
			rs=stm.executeQuery(sql.toString());
			goodsList=(List<Goodsinfo>)handler.handle(rs); 

			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBUtil.close(rs,stm);
		}

		return goodsList;
	}
}
